package k.dao.base;

import k.dto.Grid;
import k.dto.GridLocation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * Created by 隔壁老王 on 2018/4/15.
 */
@Component
public class GridDao {
    @Autowired
    BaseDao baseDao;
    String sql;
    public List<Grid> queryAllGrid(){
        sql = "  ";
        List<Grid> grids =baseDao.query(sql,Grid.class) ;
        return grids;
    }
    public List<Grid> queryGrids(){
        sql = " SELECT g.grid_id,g.grid_name,g.cdate,g.grid_proportion,g.remark,e.emp_name,o.org_name FROM grid g LEFT JOIN emp_grid eg ON g.grid_id=eg.grid_id LEFT JOIN org o ON o.org_id=g.org_id LEFT JOIN emp e ON e.emp_id=eg.emp_id ";
        List<Grid> grids =baseDao.query(sql,Grid.class) ;
        return grids;
    }
    public List<GridLocation> getGridLocation(String gridId){
        String sql = "select * from grid_location where grid_id ="+gridId+"  GROUP BY seq";
        return baseDao.query(sql,GridLocation.class);
    }

    public int addGrid(Grid grid,List<Grid> GridList,List<Grid> grid_memsList) {
        try {
            final String sql = "  insert into Grid (grid_name,remark,org_id,grid_proportion,cdate) values(:grid_name,:remark,:org_id,:grid_proportion,NOW())";
            Long id = baseDao.insert(sql, grid);
            final String sql2 = "insert into emp_grid values(null,:emp_id, '" + id + "')";

            baseDao.batch(sql2, GridList);
            baseDao.batch(sql2, grid_memsList);
            return id.intValue();
        }catch(Exception e){
            return -1;
        }

    }
    public int updGrid(Grid grid,List<Grid> GridList,List<Grid> grid_memsList) {
        try {
            final StringBuilder sql = new StringBuilder();
            sql.append("UPDATE Grid                         ");
            sql.append("	SET                                       ");
            sql.append("		grid_name=:grid_name,                   ");
            sql.append("		remark=:remark,       ");
            sql.append("		org_id=:org_id,         ");
            sql.append("		grid_proportion=:grid_proportion                  ");
            sql.append("	WHERE grid_id=:grid_id");
            baseDao.update(sql.toString(), grid);

            final String sql2="delete from Grid where grid_id=?";
            baseDao.delete(sql2, new Object[]{grid.getGrid_id()});
            final String sql3 = "insert into Grid values(null,:type,:emp_name,:emp_id, '" + grid.getGrid_id() + "')";

            baseDao.batch(sql3, GridList);
            baseDao.batch(sql3, grid_memsList);

            return grid.getGrid_id();
        }catch(Exception e){
            return -1;
        }

    }
    public Integer addGridLocation(GridLocation location){
        String sql = "insert into grid_location (grid_location_x,grid_location_y,grid_id,seq) values(:gridLocationX,:gridLocationY,:gridId,:seq) ";
        return  baseDao.insert(sql, location).intValue();
    }
}
